package com.turing.post.report.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.report.util.SaleCardStockDaySettlement;
import com.turing.post.report.util.SaleCardStockDaySettlementDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "report.impl.SaleCardStockDaySettlementDaoImpl")
@Remote
@Local
public class SaleCardStockDaySettlementDaoImpl implements
		SaleCardStockDaySettlementDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	public Date getMaxTranDate(String saleBranchNo, Date tranDate,
			BigInteger cardFaceId) {
		Query query = _em
				.createNativeQuery("select Max(t.tranDate) from SaleCardStockDaySettlement t "
						+ "where t.saleBranchNo = ?1 and t.tranDate < ?2 and t.cardFaceId = ?3");
		query.setParameter(1, saleBranchNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, cardFaceId);
		return (Date) query.getSingleResult();
	}

	public SaleCardStockDaySettlement getSaleCardStockDaySettlementByKey(
			SaleCardStockDaySettlement scsds) {
		return _em.find(SaleCardStockDaySettlement.class, scsds);
	}

	public void insert(SaleCardStockDaySettlement scsds)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, scsds);
	}

	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void removeAllSaleCardStockDaySettlementByTranDate(
			String saleBranchNo, Date tranDate) {
		Query query = _em
				.createNativeQuery("delete from SaleCardStockDaySettlement t "
						+ "where t.saleBranchNo = ?1 and t.tranDate = ?2");
		query.setParameter(1, saleBranchNo);
		query.setParameter(2, tranDate);
		query.executeUpdate();
	}

	/**
	 * 卡-5 卡库存日结表(销售机构)
	 * 
	 * @param webInput
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleCardStockDaySettlementReportByCondition(
			PCPSWebInput webInput, String branchNoCond, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqltime = "";
		if (!PCPSUtil.isEmpty(startTime))
			sqltime += " and trandate >= to_date('" + startTime
					+ "','yyyy-MM-dd') ";
		if (!PCPSUtil.isEmpty(endTime)) {
			sqltime += " and trandate <= to_date('" + endTime
					+ "','yyyy-MM-dd') ";
		}
		String sql = "select salebranchno,cardfaceid,nvl(sumlastbalance, 0),initialin,transferin,surplusin,transferout,saleout,reissueout,lossdamageout,(nvl(sumlastbalance, 0) + transferin + surplusin - transferout - saleout - reissueout - lossdamageout) balance"
				+ " from (select t.salebranchno,t.cardfaceid,(select nvl(sum(a.balance),0) from SaleCardStockDaySettlement a where a.trandate =(select max(b.trandate) from SaleCardStockDaySettlement b where b.trandate <to_date('"
				+ startTime
				+ "', 'yyyy-mm-dd') and a.salebranchno = b.salebranchno) and a.salebranchno = t.salebranchno and a.cardfaceid = t.cardfaceid) sumlastbalance,"
				+ " 0 initialin,sum(t.transferin) transferin,sum(t.surplusin) surplusin,sum(t.transferout) transferout,sum(t.saleout) saleout,sum(t.reissueout) reissueout,sum(t.lossdamageout) lossdamageout,sum(t.balance) balance"
				+ " from SaleCardStockDaySettlement t where salebranchno in ("
				+ branchNoCond
				+ ")"
				+ sqltime
				+ " group by t.salebranchno, t.cardfaceid) order by salebranchno";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 卡-5 卡库存日结表(发卡机构)
	 * 
	 * @param webInput
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getIssueCardStockDaySettlementReportByCondition(
			PCPSWebInput webInput, String branchNoCond, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqltime = "";
		if (!PCPSUtil.isEmpty(startTime))
			sqltime += " and trandate >= to_date('" + startTime
					+ "','yyyy-MM-dd') ";
		if (!PCPSUtil.isEmpty(endTime)) {
			sqltime += " and trandate <= to_date('" + endTime
					+ "','yyyy-MM-dd') ";
		}
		String sql = "select issuebranchno,cardfaceid,nvl(sumlastbalance, 0),initialin,transferin,surplusin,transferout,saleout,reissueout,lossdamageout,(nvl(sumlastbalance, 0) + initialin + transferin + surplusin -transferout - lossdamageout) balance"
				+ " from (select t.issuebranchno,t.cardfaceid,"
				+ " (select nvl(sum(a.balance),0) from issuecardstockdaysettlement a where a.trandate =(select max(b.trandate) from issuecardstockdaysettlement b where b.trandate <to_date('"
				+ startTime
				+ "', 'yyyy-mm-dd') and a.issuebranchno = b.issuebranchno) and a.issuebranchno = t.issuebranchno and a.cardfaceid = t.cardfaceid) sumlastbalance,"
				+ "sum(t.initialin) initialin,sum(t.transferin) transferin,sum(t.surplusin) surplusin,sum(t.transferout) transferout,0 saleout,0 reissueout,sum(t.lossdamageout) lossdamageout,sum(t.balance) balance from issuecardstockdaysettlement t"
				+ " where issuebranchno in ("
				+ branchNoCond
				+ ")"
				+ sqltime
				+ " group by t.issuebranchno, t.cardfaceid) order by issuebranchno";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 卡-5 卡库存结余表 合计
	 * 
	 * @param branchNoCond
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Override
	public Integer[] getCardStockCount(String branchNoCond, String startTime,
			String endTime) {
		List<Object[]> objList = getCardStockCountByCondition(branchNoCond,
				startTime, endTime);
		Object[] objs = null;
		List<Object[]> counts = null;
		Integer lastbalance = 0;
		Integer balance = 0;
		Integer[] result = new Integer[2];
		for (int i = 0; i < objList.size(); i++) {
			objs = objList.get(i);
			try {
				counts = getCountByConditon(objs[0].toString(), PCPSUtil
						.getPackedYMDString2(PCPSUtil.parseYMDDateTime(objs[1]
								.toString())));
			} catch (Exception e) {
				e.printStackTrace();
			}
			for (Object[] objArr : counts) {
				lastbalance += Integer.parseInt(objArr[0].toString());
				balance += Integer.parseInt(objArr[1].toString());
			}
		}
		result[0] = lastbalance;
		result[1] = balance;
		return result;
	}

	@SuppressWarnings("unchecked")
	private List<Object[]> getCountByConditon(String branchNo, String date) {
		String sql = "select * from (select nvl(sum(t.lastbalance),0),nvl(sum(t.balance),0) from issuecardstockdaysettlement t where t.issuebranchno ='"
				+ branchNo
				+ "' and t.trandate=to_date('"
				+ date
				+ "','yyyy-mm-dd')) "
				+ "union all (select nvl(sum(s.lastbalance),0),nvl(sum(s.balance),0) from SaleCardStockDaySettlement s where s.salebranchno='"
				+ branchNo
				+ "' and s.trandate=to_date('"
				+ date
				+ "','yyyy-mm-dd'))";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	private List<Object[]> getCardStockCountByCondition(String branchNoCond,
			String startTime, String endTime) {
		String sqltime = "";
		if (!PCPSUtil.isEmpty(startTime))
			sqltime += " and trandate >= to_date('" + startTime
					+ "','yyyy-MM-dd') ";
		if (!PCPSUtil.isEmpty(endTime)) {
			sqltime += " and trandate <= to_date('" + endTime
					+ "','yyyy-MM-dd') ";
		}
		String sql = "select * from "
				+ "(select s.salebranchno,max(s.trandate) trandate from SaleCardStockDaySettlement s where s.salebranchno in("
				+ branchNoCond
				+ ") "
				+ sqltime
				+ " group by s.salebranchno) "
				+ "union all "
				+ "(select i.issuebranchno,max(i.trandate) from issuecardstockdaysettlement i where i.issuebranchno in("
				+ branchNoCond + ") " + sqltime + " group by i.issuebranchno)";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		return list;
	}
}